# -*- coding:utf-8 -*-
# @Script: orm-hello.py
# @Author: ystlong
# @Email: ystlong@163.com
# @Create At: 2019-10-08 20:37:31
# @Last Modified By: ystlong
# @Last Modified At: 2019-10-08 21:51:02
# @Description: This is description.


import _3rpt
import sqlalchemy
import time

# from sqlalchemy import *
from sqlalchemy import create_engine
from sqlalchemy import (
    Column,
    Integer,
    String,
    Text,
    ForeignKey,
    DateTime,
    UniqueConstraint,
    Index,
)
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

print(sqlalchemy.__version__)

# engine = create_engine("sqlite:///:memory:", echo=True)

# engine = create_engine('mysql+pymysql://localhost/test', echo=True)
# engin = create_engine('mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>', echo=True)
engine = create_engine("mysql+mysqlconnector://root:123456@172.17.0.2/test", echo=True)


class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=False)
    count = Column(Integer, nullable=False)
    # name = Column(String(32), nullable=False)


def init_db():
    # Base.metadata.Create()
    Base.metadata.create_all(engine)

def drop_db():
    Base.metadata.drop_all(engine)

def insert_data():
    Session = sessionmaker(bind=engine)
    session = Session()
    user1 = User(name="test", count=1)
    user2 = User(name="test2", count=1)
    session.add(user1)
    session.add(user2)
    session.commit()
    session.close()


def query():
    def out_print_ret(rest, msg):
        print(msg)
        try:
            for k in rest:
                print(k.id, k.name)
        except TypeError:
            print(rest.id, rest.name)

    Session = sessionmaker(bind=engine)
    session = Session()
    r0 = session.query(User).all()
    out_print_ret(r0, "query all:")
    r0 = session.query(User).filter(User.id.between(0, 7), User.name == "test").all()
    out_print_ret(r0, "query filter:")

    # select for update
    r0 = session.query(User).filter(User.id == 1).with_for_update().one()
    out_print_ret(r0, "with for update")
    name = str(time.time())
    print("update id=1 name to : [%s]"%(name))
    r0 = session.query(User).filter(User.id == r0.id).update({User.name:name})
    
    r0 = session.query(User).filter(User.name == "test2").with_for_update()[:1]
    if len(r0) > 0:
        r0 = r0[0]
    r0.name = "kkk4s"
    r0.count += 1
    out_print_ret(r0, "with for update cond limit 1")

    # local all data wait for update
    # r0 = session.query(User).with_for_update().all()

    # time.sleep(20)

    # here emulator a deadlock
    # session2 = Session()
    # r0 = session2.query(User).filter(User.id == 1).with_for_update().one()
    # out_print_ret(r0, "with for update1:")
    # session2.commit()
    # session2.close()

    session.commit()
    out_print_ret(r0, "session close")
    session.close()


# drop_db()
init_db()
insert_data()
query()
